package windowFactoryPackage;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JTextArea;

public class Query {
	//connection credentials
	static String connectionURL = "jdbc:oracle:thin:@localhost:1521:xe";
	static String connectionName = "student63";
	
	// holds progId's when searched
	static ArrayList<Integer> progId = new ArrayList<Integer>();
	static ArrayList<Integer> sourceCodeId = new ArrayList<Integer>();
	static ArrayList<Integer> ipId = new ArrayList<Integer>();
	static ArrayList<String> reviewProgName = new ArrayList<String>();
	static ArrayList<String> devProgName = new ArrayList<String>();
	static ArrayList<String> progReviewUserName = new ArrayList<String>();

	// holds IDs
	static int program_id;
	static int ip_id;
	static String user_idSearch;
	static int sourceCode_idSearch;
	static boolean noDownload = false;
	static String testDownloadAvailable;

	public void userLogin() {
		char[] pass = SearchWindow.jpfPassword.getPassword();
		String password = "";
		for (int i = 0; i < pass.length; i++)
			password += pass[i];

		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = connectionURL;
			
			// Step 2: Establish connection to the Database
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT user_id id, password p FROM RSA$UserData"
							+ " WHERE LOWER(user_id) LIKE LOWER('"
							+ SearchWindow.jtfUserName.getText()
							+ "') "
							+ "AND password = '" + password + "'");

			if (rs.next()) {
				tester.UserIsLoggedIn = true;
				JOptionPane.showMessageDialog(null,
						"You have successfully logged in!");
				tester.userNameLogin = rs.getString("id");
			} else
				JOptionPane.showMessageDialog(null,"You have entered incorrect login credentials, try again.");

			conn.close();
		}

		catch (Exception ex) {
			System.err.println("Got an exception! userLogin ");
			System.err.println(ex.getMessage());

		}

	}

	public void infoWinReview() {

	}

	public void downloadCredentials() {
		noDownload = false;
		try { // Step 1: Load the JDBC driver.
			String path = "";
			String name = "";
			int size = 0;
			Class.forName("oracle.jdbc.driver.OracleDriver"); 
			
			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT NVL(digitalfilelocation,0) filePath, NVL(digitalfilename,0) fileName, NVL(digitalbytesize,0) fileSize FROM rsa$program"
							+ " WHERE program_id = '" + program_id + "'");

			if (rs.next()) {
				path = rs.getString("filePath");
				name = rs.getString("fileName");
				size = rs.getInt("fileSize");
			} else {
				JOptionPane.showMessageDialog(null,
						"There is no download for this file");
			}

			if (path.equals("0") || name.equals("0") || size == 0) {
				JOptionPane.showMessageDialog(null,
						"There is no download for this file");
				noDownload = true;
			} else {
				FileDownloader.filePath = path;
				FileDownloader.fileName = name;
				FileDownloader.fileSize = size;
			}

			conn.close();
		}

		catch (Exception ex) {
			System.err.println("Got an exception! downloadCredentials ");
			System.err.println(ex.getMessage());

		}
	}

	public void devProgWinQuery() {
		DeveloperProgramWindow.panelList.clear();
		DeveloperProgramWindow.jtaPanelList.clear();
		DeveloperProgramWindow.buttonPanelList.clear();
		DeveloperProgramWindow.jtaList.clear();
		DeveloperProgramWindow.viewInfoList.clear();
		devProgName.clear();
		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT ip.ipholdername ip_name, ip.ipholderwebaddress web, ip.ipholderphone phone, pi.name prog_name, pi.description "
							+ " FROM RSA$programinfo pi, RSA$ipholder ip"
							+ " WHERE pi.ipholder_id = ip.ipholder_id AND ip.ipholder_id = '"
							+ ip_id + "'" + " ORDER BY LOWER(pi.name)");

			while (rs.next()) {
				String substrDesc = rs.getString("description");
				int descLength = substrDesc.length();
				int substrSize = 100;

				if (descLength < 100) {
					substrSize = descLength;
				}

				substrDesc = substrDesc.substring(0, substrSize);

				// phoneNum format
				String phone = rs.getString("phone");
				String pf = "(" + phone.substring(0, 3) + ") "
						+ phone.substring(3, 6) + " - "
						+ phone.substring(6, 10);

				DeveloperProgramWindow.developerInfo[0] = rs
						.getString("ip_name");
				DeveloperProgramWindow.developerInfo[1] = rs.getString("web");
				DeveloperProgramWindow.developerInfo[2] = pf;
				DeveloperProgramWindow.jtaList.add(new JTextArea(rs
						.getString("prog_name") + "\n" + substrDesc + "..."));
				devProgName.add(rs.getString("prog_name"));
			}

			conn.close();
		}

		catch (Exception ex) {
			System.err.println("Got an exception! devProgWinQuery ");
			System.err.println(ex.getMessage());

		}
	}

	public void userDownloadUpdate() {
		int count = 0;
		// get user download count
		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT COUNT(user_id) FROM RSA$downloadedsoftware"
							+ " WHERE user_id = '" + tester.userNameLogin + "'");

			if (rs.next()) {
				count = rs.getInt("COUNT(user_id)");
			} else {
			}

			conn.close();
		}

		catch (Exception ex) {
			System.err.println("Got an exception! userDownloadCount ");
			System.err.println(ex.getMessage());

		}

		// get current date in YYYY-MMM-DD format to insert as UserSince
		final String DATE_FORMAT = "yyyy-MMM-dd";
		Calendar cal = Calendar.getInstance();
		SimpleDateFormat formatDate = new SimpleDateFormat(DATE_FORMAT);
		String currDate = formatDate.format(cal.getTime());
		// System.out.println(currDate);

		// insert user's downloaded software with count+1
		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					"zax320");
			Statement stmt = conn.createStatement();

			// query statement
			stmt.executeUpdate("INSERT INTO RSA$downloadedsoftware "
					+ "VALUES ('" + tester.userNameLogin + "','" + (count + 1)
					+ "','" + program_id + "','" + ip_id + "', " + "TO_DATE( '"
					+ currDate + "', 'YYYY-MON-DD'))");

			conn.close();
		} catch (Exception ex) {
			System.err.println("Got an exception! insert user download");
			System.err.println(ex.getMessage());

		}

	}
	
	public void programComment(){
		int count = 0;
		// get user download count
		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT COUNT(program_id) FROM RSA$reviews"
							+ " WHERE program_id = '" + program_id + "'");

			if (rs.next()) {
				count = rs.getInt("COUNT(program_id)");
			} else {
			}

			conn.close();
		}

		catch (Exception ex) {
			System.err.println("Got an exception! programComment ");
			System.err.println(ex.getMessage());

		}

		// get current date in YYYY-MMM-DD format to insert as UserSince
		final String DATE_FORMAT = "yyyy-MMM-dd";
		Calendar cal = Calendar.getInstance();
		SimpleDateFormat formatDate = new SimpleDateFormat(DATE_FORMAT);
		String currDate = formatDate.format(cal.getTime());
		// System.out.println(currDate);

		// insert user's downloaded software with count+1
		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					"zax320");
			Statement stmt = conn.createStatement();

			// query statement
			stmt.executeUpdate("INSERT INTO RSA$reviews "
					+ "VALUES ('" + (count+1) + "','" + program_id
					+ "','" + ip_id + "','" + tester.userNameLogin + "', " + "TO_DATE( '"
					+ currDate + "', 'YYYY-MON-DD'), '" + WriteReviewWindow.jta.getText() + "')");

			conn.close();
		} catch (Exception ex) {
			System.err.println("Got an exception! insert user download");
			System.err.println(ex.getMessage());

		}
	}

	public void searchProgram() {
		// clears program id, since had to initialize from the top, since first
		// run needs to be 0 results
		progId.clear();
		sourceCodeId.clear();

		// new way of searching (name, keyword, and category work together, no
		// need to distiguish between the 2)
		String name = SearchWindow.jtfKeyword.getText().toString();
		String category = SearchWindow.jcbCategory.getSelectedItem().toString();
		String keyword = SearchWindow.jcbKeyword.getSelectedItem().toString();

		String queryName = " ";
		String queryCategory = " ";
		String queryKeyword = " ";

		// if theres nothing there, don't add the AND query, else add it to
		// search for a prog name
		if (name.length() > 0) {
			queryName = " AND LOWER(pi.name) LIKE LOWER('%" + name + "%') ";
			System.out.println("Name search: " + name);
		}
		// if index is 0, you want to display all, so dont include the query,
		// else add it with the selected text
		if (SearchWindow.jcbCategory.getSelectedIndex() > 2) {
			queryCategory = " AND p.categorysearchoptions = '" + category
					+ "' ";
			System.out.println("Category: "
					+ SearchWindow.jcbCategory.getSelectedItem());
		}

		// if index is 0, you want to display all, so dont include the query,
		// else add it with the selected text
		if (SearchWindow.jcbKeyword.getSelectedIndex() > 2) {
			queryKeyword = " AND p.keywordsearchoptions = '" + keyword + "' ";
			System.out.println("Keyword: "
					+ SearchWindow.jcbKeyword.getSelectedItem());
		}

		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT p.program_id, name, price, os, rating, ipholdername, ip.ipholder_id ip_id, NVL(sourcecode_id,0) sourceCode FROM RSA$Program p, RSA$ProgramInfo pi, RSA$ipholder ip, RSA$sourcecode sc"
							+ " WHERE p.program_id = pi.program_id"
							+ " AND p.ipholder_id = ip.ipholder_id"
							+ " AND sc.program_id(+) = p.program_id"
							+ " "
							+ queryName
							+ " "
							+ queryCategory
							+ " "
							+ queryKeyword + " ORDER BY name");

			int count = 0;
			while (rs.next()) {
				progId.add(Integer.parseInt(rs.getString("program_id")));
				sourceCodeId.add(Integer.parseInt(rs.getString("sourceCode"))); 
				
				ipId.add(rs.getInt("ip_id"));
				String progName = rs.getString("name");
				// String progPrice = rs.getString("price");
				String progOs = rs.getString("os");
				String progRating = rs.getString("rating");
				String progIP = rs.getString("ipholdername"); 
				ResultsPanel.resultArrayList.add(count, "Program: " + progName
						+ " " + " | Rating: " + progRating + "\nIP Holder: "
						+ progIP + " | OS: " + progOs);

				count++; // number of results returned
			}

			conn.close();
		}

		catch (Exception ex) {
			System.err.println("Got an exception! searchProgram ");
			System.err.println(ex.getMessage());

		}
	}

	public void viewSourceCode() {
		// SourceCodeWindow.jtaSourceCode.setText(Query.)
		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT sourcecode_id s_id,sourcecodedescription sc_desc "
							+ "FROM RSA$sourcecode WHERE sourcecode_id = '"
							+ sourceCode_idSearch + "'");

			if (rs.next()) {
				SourceCodeWindow.jtaSourceCode.setText(rs.getString("sc_desc"));
			}

			conn.close();
		}

		catch (Exception ex) {
			System.err.println("Got an exception! viewSourceCode ");
			System.err.println(ex.getMessage());

		}
	}

	public void viewLiscencingInfo() {
		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT p.program_id pid, l.liscencingagreements la, l.eula eula "
							+ "FROM RSA$program p, rsa$liscence l "
							+ "WHERE p.liscence_id = l.liscence_id AND p.program_id = '"
							+ Query.program_id + "'");

			if (rs.next()) {
				LiscencesWindow.jtaLiscence.setText(rs.getString("la"));
				LiscencesWindow.jtaEULA.setText(rs.getString("eula"));
			}

			conn.close();
		}

		catch (Exception ex) {
			System.err.println("Got an exception! viewLiscencingInfo ");
			System.err.println(ex.getMessage());

		}
	}

	static String progName = "";

	//see if a program has an available file to download, if so
	//show download window, if not show message dialog -> no file
	public void testDownloadAvailable() {
		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT NVL(digitalfilelocation ,0) dfl FROM RSA$program p WHERE program_id = '"
							+ program_id + "'");

			if (rs.next()) {
				testDownloadAvailable = rs.getString("dfl");
			}

			conn.close();
		}

		catch (Exception ex) {
			System.err.println("Got an exception! testDownloadAvailable ");
			System.err.println(ex.getMessage());

		}
	}
	
	public void searchWindowDownload() {
		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT name FROM RSA$programinfo p WHERE program_id = '"
							+ program_id + "'");

			if (rs.next()) {
				progName = rs.getString("name");
				DownloadWindow.jlblOutput.setText("You are about to download "
						+ progName + "!");
			}

			conn.close();
		}

		catch (Exception ex) {
			System.err.println("Got an exception! searchWindowDownload ");
			System.err.println(ex.getMessage());

		}
	}

	public void detailedProgram() {

		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT name, releasedate, progsize, os, rating, ipholdername, ipholderwebaddress, ipholderphone, description FROM RSA$ProgramInfo pi, RSA$IPHolder ip"
							+ " WHERE pi.ipholder_id = ip.ipholder_id AND pi.program_id = '"
							+ Query.program_id + "'");

			if (rs.next()) {
				// add all of the result info to InformationWindow
				InformationWindow.jta1.setText(rs.getString("name") + "\n\n"
						+ rs.getString("description"));
				Date d = rs.getDate("releasedate");
				final String DATE_FORMAT = "yyyy-MMM-dd";
				SimpleDateFormat formatDate = new SimpleDateFormat(DATE_FORMAT);
				String relDate = formatDate.format(d);

				InformationWindow.progInfoArray[0] = relDate;
				InformationWindow.progInfoArray[1] = rs.getString("progsize")
						+ " GB";
				InformationWindow.progInfoArray[2] = rs.getString("os");
				InformationWindow.progInfoArray[3] = rs.getString("rating")
						+ "/5";

				InformationWindow.IPHolderInfoArray[0] = rs
						.getString("ipholdername");
				InformationWindow.IPHolderInfoArray[1] = rs
						.getString("ipholderwebaddress");
				InformationWindow.IPHolderInfoArray[2] = rs
						.getString("ipholderphone");
			}

			conn.close();
		}

		catch (Exception ex) {
			System.err.println("Got an exception! detailedProgram ");
			System.err.println(ex.getMessage());

		}
	}

	public void userDownloadsQuery() {
		// clear results
		PreviousDownloadsWindow.downloadDateList.clear();
		PreviousDownloadsWindow.downloadNumList.clear();
		PreviousDownloadsWindow.downloadProgramList.clear();

		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT ds.downloaddate downloadDate, pi.name progName, ip.ipholdername ipName"
							+ " FROM RSA$downloadedsoftware ds, RSA$programinfo pi, RSA$ipholder ip"
							+ " WHERE user_id = '"
							+ user_idSearch
							+ "' AND ds.program_id = pi.program_id"
							+ " AND ds.ipholder_id = ip.ipholder_id ORDER BY downloaddate");
			int count = 0;
			while (rs.next()) {
				String progName = rs.getString("progname");
				String ipName = rs.getString("ipname");
				Date d = rs.getDate("downloaddate");
				final String DATE_FORMAT = "yyyy-MMM-dd";
				SimpleDateFormat formatDate = new SimpleDateFormat(DATE_FORMAT);
				String date = formatDate.format(d);
				count++;

				PreviousDownloadsWindow.downloadDateList.add(new JLabel(date));
				PreviousDownloadsWindow.downloadProgramList.add(new JLabel(
						"<html><u>" + progName + "</u>" + " developed by "
								+ "<u>" + ipName + "</u></html>"));

			}

			conn.close();

			UserReviewWindow.programLabel.setText("User: " + user_idSearch);
			WindowGenerator.generateUserReviewWindow();
			System.out.println("user " + user_idSearch + " comment count: "
					+ count);
		}

		catch (Exception ex) {
			System.err.println("Got an exception! userReviewsQuery ");
			System.err.println(ex.getMessage());

		}
	}

	public void userPurchasesQuery() {
		// clear data
		PreviousPurchasesWindow.purchaseNumList.clear();
		PreviousPurchasesWindow.purchasePriceList.clear();
		PreviousPurchasesWindow.purchaseProgramList.clear();

		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT p.purchasedate purchaseDate, pi.name progName, pi.price"
							+ " FROM RSA$purchase p, RSA$programinfo pi, RSA$salesreceipt sr"
							+ " WHERE p.program_id = pi.program_id AND p.purchase_id = sr.purchase_id"
							+ " AND sr.user_id = '"
							+ user_idSearch
							+ "' ORDER BY purchasedate");
			int count = 0;
			while (rs.next()) {
				String progName = rs.getString("progname");
				String progCost = "$" + rs.getString("price");
				Date d = rs.getDate("purchasedate");
				final String DATE_FORMAT = "yyyy-MMM-dd";
				SimpleDateFormat formatDate = new SimpleDateFormat(DATE_FORMAT);
				String date = formatDate.format(d);
				count++;

				PreviousPurchasesWindow.purchasePriceList.add(new JLabel(
						progCost));
				PreviousPurchasesWindow.purchaseProgramList.add(new JLabel(
						"<html><u>" + progName + "</u>" + " puchased on "
								+ "<u>" + date + "</u></html>"));

			}

			conn.close();

			UserReviewWindow.programLabel.setText("User: " + user_idSearch);
			WindowGenerator.generateUserReviewWindow();
			System.out.println("user " + user_idSearch + " comment count: "
					+ count);
		}

		catch (Exception ex) {
			System.err.println("Got an exception! userReviewsQuery ");
			System.err.println(ex.getMessage());

		}
	}

	public void userReviewsQuery() {
		// clear results for each search of user comments
		UserReviewWindow.reviewsList.clear();
		UserReviewWindow.programLabelList.clear();
		UserReviewWindow.programButtonList.clear();
		UserReviewWindow.reviewsButtonPanelList.clear();
		UserReviewWindow.reviewsPanelList.clear();
		reviewProgName.clear();
		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT user_id, pi.name progName, reviewdate, reviewdescription FROM RSA$reviews r, RSA$programinfo pi"
							+ " WHERE user_id = '"
							+ user_idSearch
							+ "' AND r.program_id = pi.program_id ORDER BY reviewdate");
			int count = 0;
			while (rs.next()) {
				Date d = rs.getDate("reviewdate");
				final String DATE_FORMAT = "yyyy-MMM-dd";
				SimpleDateFormat formatDate = new SimpleDateFormat(DATE_FORMAT);
				String date = formatDate.format(d);
				count++;
				System.out.println("found user " + user_idSearch + " comment");
				UserReviewWindow.reviewsList.add(new JTextArea(rs.getString("reviewdescription")));
				UserReviewWindow.programLabelList.add(new JLabel(rs.getString("progName") + " reviewed on " + date));
				reviewProgName.add(rs.getString("progName"));
			}

			conn.close();

			UserReviewWindow.programLabel.setText("User: " + user_idSearch);
			WindowGenerator.generateUserReviewWindow();
			System.out.println("user " + user_idSearch + " comment count: "
					+ count);
		}

		catch (Exception ex) {
			System.err.println("Got an exception! userReviewsQuery ");
			System.err.println(ex.getMessage());

		}
	}

	public void searchUser() {
		String search = SearchWindow.jtfKeyword.getText();

		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT ud.*, NVL(upi.CreditCard#,1111111111111111) creditcard#, NVL(upi.creditcard_exp,to_date('19901231','YYYYMMDD')) creditcard_exp FROM RSA$UserData ud, RSA$UserPurchaseInfo upi"
							+ " WHERE ud.user_id = upi.user_id"
							+ " AND LOWER(ud.user_id) LIKE LOWER('"
							+ search
							+ "')");

			if (rs.next()) {
				user_idSearch = rs.getString("user_id");
				String creditCard = rs.getString("creditcard#");
				String cf = creditCard.substring(0, 4) + " "
						+ creditCard.substring(4, 8) + " "
						+ creditCard.substring(8, 12) + " "
						+ creditCard.substring(12, 16);
				String phone = rs.getString("userphone");
				String pf = "(" + phone.substring(0, 3) + ") "
						+ phone.substring(3, 6) + " - "
						+ phone.substring(6, 10);

				Date d = rs.getDate("creditcard_exp");
				final String DATE_FORMAT = "yyyy-MMM-dd";
				SimpleDateFormat formatDate = new SimpleDateFormat(DATE_FORMAT);
				String cardExp = formatDate.format(d);

				d = rs.getDate("usersince");
				String userSince = formatDate.format(d);
				// add results to UserWindow, display
				UserWindow.userData[0] = (rs.getString("fname") + " " + rs
						.getString("lname"));
				UserWindow.userData[1] = (rs.getString("userstreet"));
				UserWindow.userData[2] = (rs.getString("usercity") + " "
						+ rs.getString("userstate") + " " + rs
						.getString("userzip"));
				UserWindow.userData[3] = (rs.getString("useremail"));
				UserWindow.userData[4] = (pf);
				UserWindow.userData[5] = (cf + " " + "  Card EXP: " + cardExp);
				UserWindow.userData[6] = userSince;

				WindowGenerator.generateUserWindow();

			} else
				JOptionPane.showMessageDialog(null, "No user of name " + search
						+ " exists!");

			conn.close();
		}

		catch (Exception ex) {
			System.err.println("Got an exception! searchUser ");
			System.err.println(ex.getMessage());

		}

		System.out.println("user searched: " + user_idSearch);

	}

	public void infoWindowProgReview() {
		// clear data
		ReviewWindow.reviewsButtonPanelList.clear();
		ReviewWindow.reviewsList.clear();
		ReviewWindow.reviewsPanelList.clear();
		ReviewWindow.userButtonList.clear();
		ReviewWindow.userIDList.clear();
		ReviewWindow.userLabelList.clear();
		progReviewUserName.clear();
		
		//get program name and rating even if no review exists
		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT pi.name progName, pi.rating"
							+ " FROM RSA$programinfo pi"
							+ " WHERE pi.program_id = '"
							+ program_id + "'");
			int count = 0;
			while (rs.next()) {
				
				ReviewWindow.programLabel.setText(rs.getString("progName"));
				ReviewWindow.ratingLabel.setText(rs.getString("rating") + "/5");
			}

			conn.close();
		}

		catch (Exception ex) {
			System.err.println("Got an exception! userReviewsQuery ");
			System.err.println(ex.getMessage());

		}

		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT pi.name progName, pi.rating, r.reviewdescription, r.user_id, r.reviewdate"
							+ " FROM RSA$reviews r, RSA$programinfo pi"
							+ " WHERE r.program_id = pi.program_id AND r.program_id = '"
							+ program_id + "'" + " ORDER BY reviewnumber");
			int count = 0;
			while (rs.next()) {
				
				ReviewWindow.programLabel.setText(rs.getString("progName"));
				ReviewWindow.ratingLabel.setText(rs.getString("rating") + "/5");
				
				Date d = rs.getDate("reviewdate");
				final String DATE_FORMAT = "yyyy-MMM-dd";
				SimpleDateFormat formatDate = new SimpleDateFormat(DATE_FORMAT);
				String date = formatDate.format(d);
				count++;

				ReviewWindow.reviewsList.add(new JTextArea(rs.getString("reviewdescription")));
				ReviewWindow.userIDList.add(new JLabel(rs.getString("user_id") + " on " + date));
				progReviewUserName.add(rs.getString("user_id"));
			}

			conn.close();

			UserReviewWindow.programLabel.setText("User: " + user_idSearch);
			WindowGenerator.generateUserReviewWindow();
			System.out.println("user " + user_idSearch + " comment count: "
					+ count);
		}

		catch (Exception ex) {
			System.err.println("Got an exception! userReviewsQuery ");
			System.err.println(ex.getMessage());

		}

	}

	public void infoWindowDevInfo() {
		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT ipholder_id id, ipholdername name, ipholderstreet street, ipholdercity city, ipholderstate state, ipholderzip zip, ipholderphone phone, ipholderwebaddress web "
							+ "FROM rsa$ipholder ip WHERE ip.ipholder_id = '"
							+ Query.ip_id + "'");

			if (rs.next()) {
				// make phone nice
				String phone = rs.getString("phone");
				String pf = "(" + phone.substring(0, 3) + ") "
						+ phone.substring(3, 6) + " - "
						+ phone.substring(6, 10);

				// add ip_id to Query.ip_id to use
				ip_id = rs.getInt("id");
				DeveloperWindow.developerData[0] = rs.getString("name");
				DeveloperWindow.developerData[1] = rs.getString("street");
				DeveloperWindow.developerData[2] = rs.getString("city") + " "
						+ rs.getString("state") + " " + rs.getString("zip");
				DeveloperWindow.developerData[3] = pf;
				DeveloperWindow.developerData[4] = rs.getString("web");
			}

			conn.close();
		}

		catch (Exception ex) {
			System.err.println("Got an exception! searchIP ");
			System.err.println(ex.getMessage());

		}
		System.out.println("searched IP: " + ip_id);
	}

	public void searchIP() {
		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT ipholder_id id, ipholdername name, ipholderstreet street, ipholdercity city, ipholderstate state, ipholderzip zip, ipholderphone phone, ipholderwebaddress web from rsa$ipholder ip "
							+ " WHERE LOWER(ip.ipholdername) LIKE LOWER('%"
							+ SearchWindow.jtfKeyword.getText().toString()
							+ "%')");

			if (rs.next()) {
				// make phone nice
				String phone = rs.getString("phone");
				String pf = "(" + phone.substring(0, 3) + ") "
						+ phone.substring(3, 6) + " - "
						+ phone.substring(6, 10);

				// add ip_id to Query.ip_id to use
				ip_id = rs.getInt("id");
				DeveloperWindow.developerData[0] = rs.getString("name");
				DeveloperWindow.developerData[1] = rs.getString("street");
				DeveloperWindow.developerData[2] = rs.getString("city") + " "
						+ rs.getString("state") + " " + rs.getString("zip");
				DeveloperWindow.developerData[3] = pf;
				DeveloperWindow.developerData[4] = rs.getString("web");

				WindowGenerator.generateDeveloperWindow();
			}

			conn.close();
		}

		catch (Exception ex) {
			System.err.println("Got an exception! searchIP ");
			System.err.println(ex.getMessage());

		}
		System.out.println("searched IP: " + ip_id);
	}

	public static void getKeywords() {

		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT distinct keywordsearchoptions k FROM RSA$program ORDER BY k");

			while (rs.next()) {
				tester.keywordNames.add(rs.getString("k"));
			}

			conn.close();
		}

		catch (Exception ex) {
			System.err.println("Got an exception! getKeywords ");
			System.err.println(ex.getMessage());

		}

	}

	public static void getCategories() {

		try { // Step 1: Load the JDBC driver.
			Class.forName("oracle.jdbc.driver.OracleDriver"); 

			// Step 2: Establish connection to the Database
			String url = connectionURL;
			Connection conn = DriverManager.getConnection(url, connectionName,
					Credentials.pass);
			Statement stmt = conn.createStatement();

			// query statement
			ResultSet rs = stmt
					.executeQuery("SELECT distinct categorysearchoptions c FROM RSA$program ORDER BY c");

			while (rs.next()) {
				tester.categoryNames.add(rs.getString("c"));
			}

			conn.close();
		}

		catch (Exception ex) {
			System.err.println("Got an exception! getCategories ");
			System.err.println(ex.getMessage());

		}
	}

}